<?php
namespace app\model\customer;

use app\model\BaseModel;
use think\facade\Db;

/**
 * 客户模型
 * 对应数据库表: oa_customer (客户表)
 */
class Customer extends BaseModel
{
    // 设置表名（不包含前缀）
    protected $name = 'customer';
    
    // 设置主键
    protected $pk = 'id';
    
    // 设置字段类型
    protected $type = [
        'customer_type' => 'integer',
        'source_id' => 'integer',
        'grade_id' => 'integer',
        'industry_id' => 'integer',
        'services_id' => 'integer',
        'provinceid' => 'integer',
        'cityid' => 'integer',
        'distid' => 'integer',
        'townid' => 'integer',
        'customer_status' => 'integer',
        'intent_status' => 'integer',
        'contact_first' => 'integer',
        'admin_id' => 'integer',
        'belong_uid' => 'integer',
        'belong_did' => 'integer',
        'belong_time' => 'datetime',
        'distribute_time' => 'datetime',
        'follow_time' => 'datetime',
        'next_time' => 'datetime',
        'discard_time' => 'datetime',
        'is_lock' => 'integer',
        'create_time' => 'int',
        'update_time' => 'int',
        'is_primary' => 'integer',
        'group_code' => 'string',
        'controller_name' => 'string',
        'relation_desc' => 'string'
    ];

    // 可搜索字段
    protected $searchField = [
        'name',
        'customer_type',
        'grade_id',
        'industry_id',
        'customer_status',
        'belong_uid',
        'belong_did',
        'group_code',
        'controller_name'
    ];

    /**
     * 搜索客户
     * @param string $keyword 搜索关键词
     * @return array
     */
    public function searchCustomers($keyword)
    {
        // 构建查询条件
        $where = [];
        $where[] = ['c.delete_time', '=', 0];
        $where[] = ['c.discard_time', '=', 0];

        // 使用现有的searchCustomerIdsByKeywords方法获取匹配的客户ID
        $customerIds = $this->searchCustomerIdsByKeywords($keyword, $where);

        if (empty($customerIds)) {
            return [];
        }

        // 根据ID获取客户详细信息
        $list = $this->alias('c')
            ->where('c.id', 'in', $customerIds)
            ->join('oa_customer_grade g', 'c.grade_id = g.id', 'LEFT')
            ->join('oa_customer_source s', 'c.source_id = s.id', 'LEFT')
            ->join('oa_admin a', 'c.belong_uid = a.id', 'LEFT')
            ->field('c.id, c.name, c.customer_type, c.grade_id, c.source_id, c.belong_uid, c.create_time, g.title as grade_name, s.title as source_name, a.name as belong_name')
            ->order('c.id', 'desc')
            ->select()
            ->toArray();

        return $list;
    }

    /**
     * 获取客户列表
     * @param array $where 查询条件
     * @param int $page 页码
     * @param int $limit 每页数量
     * @return array
     */
    public function getCustomerList($where = [], $page = 1, $limit = 10)
    {
        // 记录当前数据库连接信息
        $connection = $this->getConnection();
        trace('Database connection config: ' . json_encode($connection->getConfig()));
        
        // 输出完整的表名（带前缀）
        trace('Actual table name: ' . $this->getTable());
        
        // 先查询客户基本信息和关联信息，不使用GROUP BY
        $query = $this->alias('c')
            ->where($where)
            ->join('oa_customer_grade g', 'c.grade_id = g.id', 'LEFT')
            ->join('oa_customer_source s', 'c.source_id = s.id', 'LEFT')
            ->join('oa_admin a', 'c.belong_uid = a.id', 'LEFT')
            // 增加admin_id字段的连接，获取录入人名称
            ->join('oa_admin a2', 'c.admin_id = a2.id', 'LEFT')
            // 关联客户联系人表，获取联系人信息
            ->join('oa_customer_contact cc', 'c.id = cc.cid AND cc.is_default = 1 AND cc.delete_time = 0', 'LEFT')
            ->field('c.*, g.title as grade_name, s.title as source_name, a.name as belong_name, 
                   a2.name as creator_name,
                   cc.name as contact_name, cc.mobile as contact_mobile,
                   c.follow_time as last_follow_time')
            ->order('c.id', 'desc');
        
        // 获取总数
        $totalQuery = clone $query;
        $total = $totalQuery->count();
        trace('Total records: ' . $total);
        
        // 分页查询
        $list = $query->page($page, $limit)->select()->toArray();
        
        // 获取客户ID列表
        $customerIds = array_column($list, 'id');
        
        // 查询合同统计数据
        if (!empty($customerIds)) {
            $contractStats = Db::name('contract')
                ->where('customer_id', 'in', $customerIds)
                ->where('delete_time', 0)
                ->field('customer_id, COUNT(id) as contract_num, IFNULL(SUM(cost), 0) as contract_amount')
                ->group('customer_id')
                ->select()
                ->toArray();
            
            // 将合同统计数据转换为以客户ID为键的数组
            $contractStatsMap = [];
            foreach ($contractStats as $stat) {
                $contractStatsMap[$stat['customer_id']] = $stat;
            }
        } else {
            $contractStatsMap = [];
        }
        
        // 记录SQL日志
        $sql = $query->fetchSql()->select();
        trace('Customer list query: ' . $sql);
        
        // 使用ORM方式测试查询
        $testQuery = $this->where([
            'belong_uid' => 1,
            'delete_time' => 0,
            'discard_time' => 0
        ]);
        trace('ORM test query: ' . $testQuery->fetchSql()->select());
        $count = $testQuery->count();
        trace('ORM test result: ' . $count);
        
        // 直接查询第一条记录
        $firstRecord = $this->where('belong_uid', 1)->find();
        trace('First record: ' . json_encode($firstRecord));
        
        // 从oa_basic_customer表获取会员状态配置
        $statusConfig = Db::name('basic_customer')
            ->where('types', '1')
            ->column('title', 'id');
        
        // 处理列表数据
        $processedList = array_map(function($item) use ($statusConfig, $contractStatsMap) {
            // 合并合同统计数据
            $contractStat = $contractStatsMap[$item['id']] ?? ['contract_num' => 0, 'contract_amount' => 0];
            $item['contract_num'] = $contractStat['contract_num'];
            $item['contract_amount'] = $contractStat['contract_amount'];
            
            $item['customer_status_name'] = $statusConfig[$item['customer_status'] ?? 0] ?? '未知';
            
            // 处理跟进时间显示
            if (!isset($item['last_follow_time']) || $item['last_follow_time'] === null || $item['last_follow_time'] <= 0) {
                $item['last_follow_time'] = null;
                $item['last_follow_time_text'] = '无跟进';
            } else {
                // 确保时间戳是秒级而非毫秒级
                $timestamp = $item['last_follow_time'] > 9999999999 ? $item['last_follow_time'] / 1000 : $item['last_follow_time'];
                $item['last_follow_time_text'] = date('Y-m-d H:i:s', $timestamp);
            }
            
            // 确保联系人信息不为空
            if (!isset($item['contact_name'])) {
                $item['contact_name'] = '';
            }
            if (!isset($item['contact_mobile'])) {
                $item['contact_mobile'] = '';
            }
            
            return $item;
        }, $list);

        return [
            'list' => $processedList,
            'total' => $total,
            'page' => $page,
            'limit' => $limit
        ];
    }

    /**
     * 获取客户详情
     * @param int $id 客户ID
     * @return array|null
     */
    public function getCustomerDetail($id)
    {
        return $this->where('id', $id)->where('delete_time', 0)->find();
    }

    /**
     * 添加客户
     * @param array $data 客户数据
     * @return int
     */
    public function addCustomer($data)
    {
        return $this->insertGetId($data);
    }

    /**
     * 更新客户
     * @param int $id 客户ID
     * @param array $data 更新数据
     * @return bool
     */
    public function updateCustomer($id, $data)
    {
        $result = $this->where('id', $id)->update($data);
        return $result !== false;
    }

    /**
     * 软删除客户
     * @param int $id 客户ID
     * @return bool
     */
    public function softDeleteCustomer($id)
    {
        $result = $this->where('id', $id)->update(['delete_time' => time()]);
        return $result !== false;
    }

    /**
     * 废弃/恢复客户
     * @param int $id 客户ID
     * @param bool $isDiscard 是否废弃
     * @return bool
     */
    public function discardCustomer($id, $isDiscard = true)
    {
        $data = $isDiscard ? ['discard_time' => time()] : ['discard_time' => 0];
        $result = $this->where('id', $id)->update($data);
        return $result !== false;
    }

    /**
     * 根据关键词搜索客户ID
     * @param string $keywords 搜索关键词
     * @param array $where 额外的查询条件
     * @return array 匹配的客户ID数组
     */
    public function searchCustomerIdsByKeywords($keywords, $where = [])
    {
        // 查询客户表本身的字段
        $customerQuery = $this->alias('c')
            ->where($where)
            ->where(function($query) use ($keywords) {
                $query->where('c.name', 'like', '%' . $keywords . '%')
                      ->whereOr('c.address', 'like', '%' . $keywords . '%')
                      ->whereOr('c.remark', 'like', '%' . $keywords . '%');
            })
            ->field('c.id');

        $customerIds = array_column($customerQuery->select()->toArray(), 'id');

        // 查询关联的联系人表
        $contactCustomerIds = Db::name('customer_contact')
            ->alias('cc')
            ->join('customer c', 'cc.cid = c.id')
            ->where('c.delete_time', 0)
            ->where($where)
            ->where(function($query) use ($keywords) {
                $query->where('cc.name', 'like', '%' . $keywords . '%')
                      ->whereOr('cc.mobile', 'like', '%' . $keywords . '%')
                      ->whereOr('cc.email', 'like', '%' . $keywords . '%')
                      ->whereOr('cc.qq', 'like', '%' . $keywords . '%')
                      ->whereOr('cc.wechat', 'like', '%' . $keywords . '%');
            })
            ->field('c.id')
            ->select()->toArray();

        $contactIds = array_column($contactCustomerIds, 'id');

        // 合并两个结果集并去重
        $allIds = array_unique(array_merge($customerIds, $contactIds));

        return $allIds;
    }
}